﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace KorleBu
{
    public class DataTier
    {

        #region constructor
        
        public DataTier()
        {

        }

        #endregion

        #region variables

        private string _connectionstring;

        #endregion
        
        #region mainMethods

        public string Connectionstring
        {
            get
            {
                if (_connectionstring == null)
                    _connectionstring = ConfigurationManager.ConnectionStrings["KorleBu_STA_InvetoryConnectionstring"].ConnectionString;

                return _connectionstring;
            }
        }

        protected DataSet GetDataSet(SqlCommand command)
        {
            DataSet set = new DataSet();

            using (SqlConnection connection = new SqlConnection(this.Connectionstring))
            {
                connection.Open();
                command.Connection = connection;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(set);
                connection.Close();
            }

            return set;
        }

        protected DataTable GetDataTable(SqlCommand command)
        {
            DataTable table = new DataTable();

            using (SqlConnection connection = new SqlConnection(this.Connectionstring))
            {
                connection.Open();
                command.Connection = connection;

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                connection.Close();
            }

            return table;
        }
        #endregion

        #region storedProcMethods

        #region login
        /// Michael Meyer
        public DataSet Login(string userName, string password, string ipAddress)
        {
            SqlCommand command = new SqlCommand("sp_login");
            command.Parameters.AddWithValue("@username", userName);
            command.Parameters.AddWithValue("@password", password);
            command.Parameters.AddWithValue("@ipaddress", ipAddress);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet GetHint(string userName)
        {
            SqlCommand command = new SqlCommand("sp_getHint");
            command.Parameters.AddWithValue("@username", userName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet IsValidSession(string sessionID)
        {
            SqlCommand command = new SqlCommand("sp_isValidSession");
            command.Parameters.AddWithValue("@sessionID", sessionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet Logout(string sessionID)
        {
            SqlCommand command = new SqlCommand("sp_logout");
            command.Parameters.AddWithValue("@sessionID", sessionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }        

        #endregion

        #region items
        //Michael Meyer
        public DataSet AllocateNewItem(string itemName, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_allocateNewItem");
            command.Parameters.AddWithValue("@itemName", itemName);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet AddItem(string itemName, int categoryID, int unitID, double vendorCost, double markupPercent, int emergencyQuantity, int minStockQty, int vendorID, int storeID, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_addItem");
            command.Parameters.AddWithValue("@itemName", itemName);
            command.Parameters.AddWithValue("@unitID", unitID);
            command.Parameters.AddWithValue("@vendorCost", vendorCost);
            command.Parameters.AddWithValue("@itemCategoryID", categoryID);
            command.Parameters.AddWithValue("@emergencyQuantity", emergencyQuantity);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@markupPercent", markupPercent);
            command.Parameters.AddWithValue("@vendorID", vendorID);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.Parameters.AddWithValue("@minStockQty", minStockQty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// <summary>
        /// 11-24-12 Adam Venezia; When modifying an item, we need to fill in the existing data to a modify template.
        /// </summary>
        /// <param name="itemID">Item ID to query the database against.</param>
        /// <returns>DataSet containing Error table[0] and Data table[1].</returns>
        public DataSet GetModifyInfoByID(int itemID)
        {
            SqlCommand command = new SqlCommand("sp_getModifyItemInfo");
            command.Parameters.AddWithValue("@itemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        
        /// <summary>
        /// 11-24-12 Adam Venezia; Committing modify Item.
        /// </summary>
        /// <returns>DataSet table[0] with errors, table[1] with 1 = completed, 0 = not completed</returns>
        public DataSet ModifyItem(int itemID, string itemName, int unitID, double vendorCost,
            int itemCategoryID, int emergencyQty, int storeID, double markupPercent, int vendorID, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_modifyItem");
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@itemName", itemName);
            command.Parameters.AddWithValue("@unitID", unitID);
            command.Parameters.AddWithValue("@vendorCost", vendorCost);
            command.Parameters.AddWithValue("@itemCategoryID", itemCategoryID);
            command.Parameters.AddWithValue("@emergencyQuantity", emergencyQty);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@markupPercent", markupPercent);
            command.Parameters.AddWithValue("@vendorID", vendorID);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        /// Overloaded to not include modifying totalQuantity
        //public DataSet ModifyItem(int itemID, int unitID, string itemName, int statusID, double markupCost, int itemCategoryID, int storeID)
        //{
        //    SqlCommand command = new SqlCommand("sp_modifyItem");
        //    command.Parameters.AddWithValue("@itemID", itemID);
        //    command.Parameters.AddWithValue("@itemName", itemName);
        //    command.Parameters.AddWithValue("@statusID", statusID);
        //    command.Parameters.AddWithValue("@markupCost", markupCost);
        //    command.Parameters.AddWithValue("@itemCategoryID", itemCategoryID);
        //    command.Parameters.AddWithValue("@unitID", unitID);
        //    command.Parameters.AddWithValue("@storeID", storeID);
        //    command.CommandType = CommandType.StoredProcedure;

        //    return GetDataSet(command);
        //}

        /// Michael Meyer
        public DataSet AddCategory(string categoryName, string storeID)
        {
            SqlCommand command = new SqlCommand("sp_addCategory");
            command.Parameters.AddWithValue("@categoryName", categoryName);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetCategory()
        {
            SqlCommand command = new SqlCommand("sp_getCategory");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Jason Vance - Nov 23 2012
        public DataSet GetCategoryByID(int categoryID)
        {
            SqlCommand command = new SqlCommand("sp_getCategoryByID");
            command.Parameters.AddWithValue("@categoryID", categoryID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet ModifyCategory(int categoryID, string categoryName, string storeID)
        {
            SqlCommand command = new SqlCommand("sp_modifyCategory");
            command.Parameters.AddWithValue("@categoryID", categoryID);
            command.Parameters.AddWithValue("@categoryName", categoryName);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        ///Michael Meyer
        public DataSet GetItemCategories()
        {
            SqlCommand command = new SqlCommand("sp_getItemCategories");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetUnits()
        {
            SqlCommand command = new SqlCommand("sp_getUnits");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Michael Meyer
        public DataSet GetItemByName(string itemName)
        {
            SqlCommand command = new SqlCommand("sp_getItemByName");
            command.Parameters.AddWithValue("@itemName", itemName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet GetItemByCategory(Int32 itemCategory)
        {
            SqlCommand command = new SqlCommand("sp_getItemByCategory");
            command.Parameters.AddWithValue("@itemCategoryID", itemCategory);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets all of the items in the database
        /// </summary>
        /// <returns>Table 1 for errors, table 2 with the item id and item name</returns>
        public DataSet GetItems()
        {
            SqlCommand command = new SqlCommand("sp_getItems");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region vendors

        /// <summary>
        /// Gets vendor by the id sent in
        /// </summary>
        /// <param name="vendorID">Vendor that you are looking for</param>
        /// <returns>table 1 - errors, table 2 - int vendorID, string vendorName, string vendorAddress, string phone, int statusID</returns>
        public DataSet GetVendorByID(int vendorID)
        {
            SqlCommand command = new SqlCommand("sp_getVendorByID");
            command.Parameters.AddWithValue("@vendorID", vendorID);
            command.CommandType = CommandType.StoredProcedure;
            
            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Adds a vendor to the database
        /// </summary>
        /// <param name="vendorName">Vendors name</param>
        /// <param name="vendorAddress">Vendors Address</param>
        /// <param name="phoneNumber">Vendors phone number</param>
        /// <returns>Table 1 - Error messages, Table 2 - 1 if success, 0 if false</returns>
        public DataSet AddVendor(string vendorName, string vendorAddress, string phoneNumber)
        {
            SqlCommand command = new SqlCommand("sp_addVendor");
            command.Parameters.AddWithValue("@vendorName", vendorName);
            command.Parameters.AddWithValue("@vendorAddress", vendorAddress);
            command.Parameters.AddWithValue("@phone", phoneNumber);
            command.Parameters.AddWithValue("@statusID", 30);   // StatusID - active for vendors
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets all vendors from the database
        /// </summary>
        /// <returns>Table 1 for errors, Table 2 - int vendorID, string vendorName, string vendorAddress, string phone, in statusID</returns>
        public DataSet GetVendors()
        {
            SqlCommand command = new SqlCommand("sp_getVendors");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Modifies the vendor whose vendorID is passed
        /// </summary>
        /// <param name="vendorID">Vendor to modify</param>
        /// <param name="vendorName">Vendors new name</param>
        /// <param name="vendorAddress">Vendors new address</param>
        /// <param name="phone">Vendors new phone number</param>
        /// <param name="statusID">Vendors new status</param>
        /// <returns>Table 1 - Error messages, Table 2 - 1 if success, 0 if false</returns>
        public DataSet ModifyVendor(int vendorID, string vendorName, string vendorAddress, string phone, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_modifyVendor");
            command.Parameters.AddWithValue("@vendorID", vendorID);
            command.Parameters.AddWithValue("@vendorName", vendorName);
            command.Parameters.AddWithValue("@vendorAddress", vendorAddress);
            command.Parameters.AddWithValue("@vendorPhone", phone);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        
        //Michael Meyer
        /// <summary>
        /// Gets all active vendors
        /// </summary>
        /// <returns>Table 1 - Error message, Table 2 - int vendorID, string vendorName</returns>
        public DataSet GetActiveVendors()
        {
            SqlCommand command = new SqlCommand("sp_getActiveVendors");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Adds an item to the vendor items table
        /// </summary>
        /// <param name="itemID">Item to be added</param>
        /// <param name="vendorID">Vendor to link with the item</param>
        /// <param name="vendorCost">Vendors cost</param>
        /// <param name="purchaseDate">Date the item was purchased</param>
        /// <param name="quantity">Number of items purchased</param>
        /// <returns>Table 1 - Error messages, Table 2 - 1 if success, 0 if false</returns>
        public DataSet AddVendorItem(int itemID, int vendorID, decimal vendorCost, DateTime purchaseDate, int quantity)
        {
            SqlCommand command = new SqlCommand("sp_addVendorItem");
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@vendorID", vendorID);
            command.Parameters.AddWithValue("@vendorCost", vendorCost);
            command.Parameters.AddWithValue("@purchaseDate", purchaseDate);
            command.Parameters.AddWithValue("@quantity", quantity);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region users

        //Michael Meyer
        /// <summary>
        /// Gets the salt for the username
        /// </summary>
        /// <param name="userName">Name of the user that you want the salt for</param>
        /// <returns>table 1 - errors, table 2 -string salt</returns>
        public DataSet GetSaltByUserName(string userName)
        {
            SqlCommand command = new SqlCommand("sp_getSaltByUserName");
            command.Parameters.AddWithValue("@userName", userName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet AddUser(string firstName, string lastName, string userName, string password, string passwordHint, int storeID, bool isAdmin, bool isCEO, bool isGeneralManager, bool isStoreManager, bool isStoreKeeper, bool isSubUnitClerk, bool isAccounting, bool isProcurement, string salt)
        {
            SqlCommand command = new SqlCommand("sp_addUsers");
            command.Parameters.AddWithValue("@FirstName", firstName);
            command.Parameters.AddWithValue("@LastName", lastName);
            command.Parameters.AddWithValue("@UserName", userName);
            command.Parameters.AddWithValue("@Password", password);
            command.Parameters.AddWithValue("@PasswordHint", passwordHint);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@isAdmin", isAdmin);
            command.Parameters.AddWithValue("@isCEO", isCEO);
            command.Parameters.AddWithValue("@isGeneralManager", isGeneralManager);
            command.Parameters.AddWithValue("@isStoreManager", isStoreManager);
            command.Parameters.AddWithValue("@isStoreKeeper", isStoreKeeper);
            command.Parameters.AddWithValue("@isSubUnitClerk", isSubUnitClerk);
            command.Parameters.AddWithValue("@isAccounting", isAccounting);
            command.Parameters.AddWithValue("@isProcurement", isProcurement);
            command.Parameters.AddWithValue("@salt", salt);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet ModifyUser(int userID, string firstName, string lastName, string userName, string passwordHint, int statusID, int storeID, bool isAdmin, bool isCEO, bool isGeneralManager, bool isStoreManager, bool isStoreKeeper, bool isSubUnitClerk, bool isAccounting, bool isProcurement)
        {
            SqlCommand command = new SqlCommand("sp_modifyUsers");
            command.Parameters.AddWithValue("@UserID", userID);
            command.Parameters.AddWithValue("@FirstName", firstName);
            command.Parameters.AddWithValue("@LastName", lastName);
            command.Parameters.AddWithValue("@UserName", userName);
            command.Parameters.AddWithValue("@PasswordHint", passwordHint);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@isAdmin", isAdmin);
            command.Parameters.AddWithValue("@isCEO", isCEO);
            command.Parameters.AddWithValue("@isGeneralManager", isGeneralManager);
            command.Parameters.AddWithValue("@isStoreManager", isStoreManager);
            command.Parameters.AddWithValue("@isStoreKeeper", isStoreKeeper);
            command.Parameters.AddWithValue("@isSubUnitClerk", isSubUnitClerk);
            command.Parameters.AddWithValue("@isAccounting", isAccounting);
            command.Parameters.AddWithValue("@isProcurement", isProcurement);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet UpdatePassword(int userID, string oldPassword, string newPassword, string salt)
        {
            SqlCommand command = new SqlCommand("sp_updatePassword");
            command.Parameters.AddWithValue("@userID", userID);
            command.Parameters.AddWithValue("@oldPassword", oldPassword);
            command.Parameters.AddWithValue("@newPassword", newPassword);
            command.Parameters.AddWithValue("@salt", salt);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet GetUserByID(int userID)
        {
            SqlCommand command = new SqlCommand("sp_getUserByID");
            command.Parameters.AddWithValue("@isUserID", userID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet GetUserByName(string firstName, string lastName)
        {
            SqlCommand command = new SqlCommand("sp_getUserByName");
            command.Parameters.AddWithValue("@FirstName", firstName);
            command.Parameters.AddWithValue("@LastName", lastName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet GetAllUsers()
        {
            SqlCommand command = new SqlCommand("sp_getAllUsers");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Michael Meyer
        public DataSet GetUsersByRole(bool isAdmin, bool isGeneralManager, bool isStoreManager, bool isStoreKeeper, bool isSubUnitClerk, bool isCEO, bool isAccounting, bool isProcurement)
        {
            SqlCommand command = new SqlCommand("sp_getUsersByRole");
            command.Parameters.AddWithValue("@isAdmin", isAdmin);
            command.Parameters.AddWithValue("@isGeneralManager", isGeneralManager);
            command.Parameters.AddWithValue("@isStoreManager", isStoreManager);
            command.Parameters.AddWithValue("@isStoreKeeper", isStoreKeeper);
            command.Parameters.AddWithValue("@isSubUnitClerk", isSubUnitClerk);
            command.Parameters.AddWithValue("@isCEO", isCEO);
            command.Parameters.AddWithValue("@isAccounting", isAccounting);
            command.Parameters.AddWithValue("@isProcurement", isProcurement);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Michael Meyer
        public DataSet GetUsersByStatusID(int statusID)
        {
            SqlCommand command = new SqlCommand("sp_getUsersByStatusID");
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region getStatuses

        /// Michael Meyer
        public DataSet GetAvailableItemStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableItemStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableStoreUnitStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableStoreUnitStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableRequisitionStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableRequisitionStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableSatelliteStoreRequestStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableSatelliteStoreRequestStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableStoreStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableStoreStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableStoreTypeStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableStoreTypeStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableSubUnitStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableSubUnitStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableUserStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableUserStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Michael Meyer
        public DataSet GetAvailableVendorStatuses()
        {
            SqlCommand command = new SqlCommand("sp_getAvailableVendorStatuses");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        #endregion

        #region stores

        // Michael Meyer
        /// <summary>
        /// Gets all stores
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetAllStores()
        {
            SqlCommand command = new SqlCommand("sp_getAllStores");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet GetStoresByType(int type)
        {
            SqlCommand command = new SqlCommand("sp_getStoresByType");
            command.Parameters.AddWithValue("@type", type);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer

        public DataSet GetStoreUnitStatus(int storeID, int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_getStoreUnitStatus");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all active central stores
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetActiveCentralStores()
        {
            SqlCommand command = new SqlCommand("sp_getActiveCentralStores");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all active sub units
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetActiveSubUnits()
        {
            SqlCommand command = new SqlCommand("sp_getActiveSubUnits");
            command.CommandType = CommandType.StoredProcedure;
            
            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets all stores by name
        /// </summary>
        /// <param name="storeName">Name of the store you are looking for</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetStoreByName(string storeName)
        {
            SqlCommand command = new SqlCommand("sp_getStoreByName");
            command.Parameters.AddWithValue("@storeName", storeName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets all stores by their ID
        /// </summary>
        /// <param name="storeID">ID of the store you are looking for</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetStoreByID(int storeID)
        {
            SqlCommand command = new SqlCommand("sp_getStoreByID");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the sub units
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetAllSubUnits()
        {
            SqlCommand command = new SqlCommand("sp_getAllSubUnits");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the sub units by their name
        /// </summary>
        /// <param name="subUnitName">Name of the sub unit</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetSubUnitByName(string subUnitName)
        {
            SqlCommand command = new SqlCommand("sp_getSubUnitByName");
            command.Parameters.AddWithValue("@subUnitName", subUnitName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the sub units by their ID
        /// </summary>
        /// <param name="subUnitID">ID of the sub unit you are looking for</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetSubUnitByID(int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_getSubUnitByID");
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Modifies the store information
        /// </summary>
        /// <param name="storeID">Store id you want to modify</param>
        /// <param name="storeName">New name of the store</param>
        /// <param name="storeTypeID">New type of the store</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet ModifyStore(int storeID, string storeName, int storeTypeID, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_modifyStore");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@storeName", storeName);
            command.Parameters.AddWithValue("@storeTypeID", storeTypeID);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Adds a new store
        /// </summary>
        /// <param name="storeName">New store name</param>
        /// <param name="storeTypeID">New store type</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet AddStore(string storeName, int storeTypeID)
        {
            SqlCommand command = new SqlCommand("sp_addStore");
            command.Parameters.AddWithValue("@storeName", storeName);
            command.Parameters.AddWithValue("@storeTypeID", storeTypeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Modifies the unit that the store belongs to
        /// </summary>
        /// <param name="storeID"></param>
        /// <param name="subUnitID"></param>
        /// <param name="statusID"></param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet ModifyStoreUnit(int storeID, int subUnitID, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_modifyStoreUnit");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Connects a store with a store unit
        /// </summary>
        /// <param name="storeID">Store ID that gets connected</param>
        /// <param name="subUnitID">Sub unit id that gets connected</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet AddStoreUnit(int storeID, int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_addStoreUnit");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Adds a new sub unit (mailbox)
        /// </summary>
        /// <param name="subUnitName">The name of the new sub unit (mailbox)</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet AddSubUnit(string subUnitName)
        {
            SqlCommand command = new SqlCommand("sp_addSubUnit");
            command.Parameters.AddWithValue("@subUnitName", subUnitName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Updates the Subunit that's id is passed in
        /// </summary>
        /// <param name="subUnitID">The sub unit you want to update</param>
        /// <param name="subUnitName">The new name of the sub unit</param>
        /// <param name="statusID">The new status of the sub unit</param>
        /// <returns>Two tables, table 1 has errors, table 2 has the results (true or false)</returns>
        public DataSet ModifySubUnit(int subUnitID, string subUnitName, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_modifySubUnit");
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.Parameters.AddWithValue("@subUnitName", subUnitName);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the store types
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetStoreTypes()
        {
            SqlCommand command = new SqlCommand("sp_getStoreTypes");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the sub units
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetSubUnits()
        {
            SqlCommand command = new SqlCommand("sp_getSubUnits");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Michael Meyer
        /// <summary>
        /// Gets all of the store units
        /// </summary>
        /// <returns>Two tables, table 1 has errors, table 2 has the results</returns>
        public DataSet GetStoreUnits()
        {
            SqlCommand command = new SqlCommand("sp_getStoreUnits");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region receiptVoucher
      
        /// Benjamin Strickler
        public DataSet CheckToCloseRequisition(int requisitionID)
        {
            SqlCommand command = new SqlCommand("sp_checkToCloseRequisition");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetReceiptVoucher(int requisitionID)
        {
            SqlCommand command = new SqlCommand("sp_getReceiptVoucher");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region requests

        public DataSet MarkRequestItemPartialFill(int requestID, int itemID)
        {
            SqlCommand command = new SqlCommand("sp_MarkRequestItemPartialFill");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.Parameters.AddWithValue("@itemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Benjamin Strickler
        public DataSet MarkRequestItemClosed(int requestID, int itemID)
        {
            SqlCommand command = new SqlCommand("sp_MarkRequestItemClosed");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.Parameters.AddWithValue("@itemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        /// Benjamin Strickler
        public DataSet ModifyRequest(int requestID, int itemID, int qty)
        {
            SqlCommand command = new SqlCommand("spModifyRequest");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@qty", qty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet CreateRequest(int StoreID, string requestDescription)
        {
            SqlCommand command = new SqlCommand("sp_CreateRequest");
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.Parameters.AddWithValue("@RequestDescription", requestDescription);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet GetRequestsByStoreID(int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_GetRequestsByStoreID");
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet CheckOpenRequest(int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_CheckOpenRequest");
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet AddItemToRequest(int requestID, int itemID, int qtyRequested)
        {
            SqlCommand command = new SqlCommand("sp_AddItemToRequest");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.Parameters.AddWithValue("@QtyRequested", qtyRequested);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet ChangeRequestDescription(int requestID, string description)
        {
            SqlCommand command = new SqlCommand("sp_ChangeRequestDescription");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.Parameters.AddWithValue("@description", description);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet SubmitRequest(int requestID)
        {
            SqlCommand command = new SqlCommand("sp_SubmitRequest");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet GetRequestItemsByRequestIDStoreManger(int requestID, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_GetRequestItemsByRequestIDStoreManger");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet GetRequestItemsByRequestID(int requestID, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_GetRequestItemsByRequestID");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet DeleteRequestItem(int requestID, int itemID)
        {
            SqlCommand command = new SqlCommand("sp_DeleteRequestItem");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet DeleteRequest(int requestID)
        {
            SqlCommand command = new SqlCommand("sp_DeleteRequest");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet ItemsandQuantity(int requestID, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_ItemsandQuantity");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet FillRequestItem(int requestID, int itemID, int qtyReceived, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_FillRequestItem");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.Parameters.AddWithValue("@QtyReceived", qtyReceived);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// <summary>
        /// Bryan Nixon
        /// </summary>
        /// <param name="requestID"></param>
        /// <param name="statusID"></param>
        /// <returns></returns>
        public DataSet MarkRequestStatusByStatusID(int requestID, int statusID)
        {
            SqlCommand command = new SqlCommand("sp_markRequestStatusByStatusID");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.Parameters.AddWithValue("@statusID", statusID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet UpdateRequestStatus(int requestID)
        {
            SqlCommand command = new SqlCommand("sp_UpdateRequestStatus");
            command.Parameters.AddWithValue("@requestID", requestID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region requisitions

        //Michael Meyer
        /// <summary>
        /// Gets the lpo for the requisition id
        /// </summary>
        /// <param name="requisitionID">Requisition that you want the LPO for</param>
        /// <returns>table 1 - results, table 2 - int lpo</returns>
        public DataSet GetLPO(int requisitionID)
        {
            SqlCommand command = new SqlCommand("sp_updateLPO");
            command.Parameters.AddWithValue("@requisitionID", requisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Updates the LPO field for the requisition
        /// </summary>
        /// <param name="requisitionID">Requisition that you want to update.</param>
        /// <param name="lpo">the value that you want to put in the lpo field</param>
        /// <returns>table 1 - errors, table 2 - true/false</returns>
        public DataSet UpdateLPO(int requisitionID, string lpo)
        {
            SqlCommand command = new SqlCommand("sp_updateLPO");
            command.Parameters.AddWithValue("@requisitionID", requisitionID);
            command.Parameters.AddWithValue("@lpo", lpo);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        //Ben
        public DataSet CreateItemAdjustmentReason(string adjReason)
        {
            SqlCommand command = new SqlCommand("spCreateItemAdjustmentReason");
            command.Parameters.AddWithValue("@adjReason", adjReason);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        // Jason Vance - 27 Nov 2012
        public DataSet ModifyItemAdjustmentReason(int adjID, string newAdjReason)
        {
            SqlCommand command = new SqlCommand("sp_modifyItemAdjustmentReason");
            command.Parameters.AddWithValue("@adjustID", adjID);
            command.Parameters.AddWithValue("@newAdjustName", newAdjReason);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Ben
        public DataSet ModifyRequisition(int requisitionID, int storeID, int itemID, int qty)
        {
            SqlCommand command = new SqlCommand("sp_modifyRequisition");
            command.Parameters.AddWithValue("@requisitionID", requisitionID);
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@qty", qty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets items by the store unit id
        /// </summary>
        /// <param name="storeUnitID">Store unit id that you want the items for</param>
        /// <returns>table 1 -errors, table 2 - int itemID, string itemName, int quantity</returns>
        public DataSet GetItemsByStoreUnitID(int storeID, int subunitID)
        {
            SqlCommand command = new SqlCommand("sp_getItemsByStoreUnitID");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@subUnitID", subunitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Benjamin Strickler
        public DataSet GetItemByNameByStore(string itemName, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_getItemByNameByStore");
            command.Parameters.AddWithValue("@itemName", itemName);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Benjamin Strickler
        public DataSet GetItemByCategoryByStore(int itemCategory, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_getItemByCategoryByStore");
            command.Parameters.AddWithValue("@itemCategoryID", itemCategory);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Benjamin Strickler
        public DataSet GetRequestItemsByRequestByStore(int requestID, int storeID)
        {
            SqlCommand command = new SqlCommand("sp_GetRequestItemsByRequestByStore");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Benjamin Strickler
        public DataSet GetRequisitionsNotClosedByStore(int storeID)
        {
            SqlCommand command = new SqlCommand("sp_GetRequisitionsNotClosedByStore");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetPendingRequests(int storeID)
        {
            SqlCommand command = new SqlCommand("sp_getPendingRequests");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet AddRequisition(int storeID, string RequisitionName)
        {
            SqlCommand command = new SqlCommand("sp_addRequisition");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.Parameters.AddWithValue("@RequisitionName", RequisitionName);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Benjamin Strickler
        public DataSet CheckOpenRequisition(int storeID)
        {
            SqlCommand command = new SqlCommand("sp_checkOpenRequisition");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionsByGMStatus()
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsByGMStatus");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionsByPendingStatus()
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsByPendingStatus");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionsByCEOStatus()
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsByCEOStatus");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionNotClosed()
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsNotClosed");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionsByOrderedStatus(int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsByOrderedStatus");
            command.Parameters.AddWithValue("@storeID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionItemsByRequisitionID(int RequisitionID, bool getDenied)
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionItemsByRequisitionID");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@getDenied", getDenied);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequisitionsNotOrdered()
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionsNotOrdered");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        /// Benjamin Strickler
        public DataSet AcceptRejectRequisitionItem(int RequisitionID, int ItemID, bool isDenied)
        {
            SqlCommand command = new SqlCommand("sp_acceptRejectRequisitionItem");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@ItemID", ItemID);
            command.Parameters.AddWithValue("@IsDenied", isDenied);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet AddCommentToRequsition(int RequisitionID, String Comments)
        {
            SqlCommand command = new SqlCommand("sp_denyWithComments");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@Comments", Comments);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        /// Benjamin Strickler
        public DataSet AcceptRequisition(int RequisitionID, int user, bool gm, bool ceo)
        {
            SqlCommand command = new SqlCommand("sp_acceptRequisition");
            command.Parameters.AddWithValue("@requisitionID", RequisitionID);
            command.Parameters.AddWithValue("@userID", user);
            command.Parameters.AddWithValue("@isGM", gm);
            command.Parameters.AddWithValue("@isCEO", ceo);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet RejectRequisition(int RequisitionID)
        {
            SqlCommand command = new SqlCommand("sp_rejectRequisition");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet AddItemToRequisition(int RequisitionID, int ItemID, int RequestedQty, int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_addItemToRequisition");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@ItemID", ItemID);
            command.Parameters.AddWithValue("@RequestedQty", RequestedQty);
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet ChangeRequisitionDescription(int RequisitionID, string Description)
        {
            SqlCommand command = new SqlCommand("sp_ChangeRequisitionDescription");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@Description", Description);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet CancelRequisition(int RequisitionID)
        {
            SqlCommand command = new SqlCommand("sp_DeleteRequisition");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        /// Preston Berrett
        public DataSet CheckSignature(int RequisitionID)
        {
            SqlCommand command = new SqlCommand("sp_checkSignature");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet SubmitRequisition(int RequisitionID)
        {
            SqlCommand command = new SqlCommand("sp_submitRequisition");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet DeleteRequisitionItem(int RequisitionID, int ItemID, int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_deleteRequisitionItem");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@ItemID", ItemID);
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet ReallocateItem(int ItemID, int StoreID, int SubUnitFromID, int SubUnitToID, int Qty)
        {
            SqlCommand command = new SqlCommand("sp_reallocateItem");
            command.Parameters.AddWithValue("@ItemID", ItemID);
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.Parameters.AddWithValue("@SubUnitFromID", SubUnitFromID);
            command.Parameters.AddWithValue("@SubUnitToID", SubUnitToID);
            command.Parameters.AddWithValue("@Qty", Qty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet ChangeRequisitionItemQty(int RequisitionID, int ItemID, int Qty)
        {
            SqlCommand command = new SqlCommand("sp_changeRequisitionItemQty");
            command.Parameters.AddWithValue("@RequisitionID", RequisitionID);
            command.Parameters.AddWithValue("@ItemID", ItemID);
            command.Parameters.AddWithValue("@Qty", Qty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet MarkItemReady(int satelliteStoreRequisitionID, int itemID, int allocated, int RequestItemID)
        {
            SqlCommand command = new SqlCommand("sp_markItemReady");
            command.Parameters.AddWithValue("@RequestID", satelliteStoreRequisitionID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.Parameters.AddWithValue("@Allocated", allocated);
            command.Parameters.AddWithValue("@RequestItemID", RequestItemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet MarkItemOrdered(int satelliteStoreRequisitionID, int itemID)
        {
            SqlCommand command = new SqlCommand("sp_markItemOrdered");
            command.Parameters.AddWithValue("@RequestID", satelliteStoreRequisitionID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet GetStoreUnitItemQty(int storeID, int subUnitID, int itemID)
        {
            SqlCommand command = new SqlCommand("sp_GetStoreUnitItemQty");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.Parameters.AddWithValue("@SubUnitID", subUnitID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        /// Preston Berrett
        public DataSet GetRequestReady(int storeID)
        {
            SqlCommand command = new SqlCommand("sp_getRequestReady");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet CompleteRequisition(int requisitionID)
        {
            SqlCommand command = new SqlCommand("sp_completeRequisition");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet GetRequestReadyItems(int requestID)
        {
            SqlCommand command = new SqlCommand("sp_getRequestReadyItems");
            command.Parameters.AddWithValue("@RequestID", requestID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet UpdateItemQuantityReceived(int requisitionID, int itemID, int quantity, int vendorID, int LPO, String recOfficer)
        {
            SqlCommand command = new SqlCommand("sp_updateItemQuantityReceived");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.Parameters.AddWithValue("@Quantity", quantity);
            command.Parameters.AddWithValue("@LPO", LPO);
            command.Parameters.AddWithValue("@VendorID", vendorID);
            command.Parameters.AddWithValue("@ReceivingOfficer", recOfficer);

            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet UpdateSRA(int requisitionID, int srano, string sra)
        {
            SqlCommand command = new SqlCommand("sp_updateSRA");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.Parameters.AddWithValue("@SRANo", srano);
            command.Parameters.AddWithValue("@SRA", sra);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        ///Preston Berrett
        public DataSet GetSRA(int requisitionID)
        {
            SqlCommand command = new SqlCommand("sp_getSRA");
            command.Parameters.AddWithValue("@RequisitionID", requisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        //Michael Meyer
        /// <summary>
        /// Adjusts the item quantity for the specified item
        /// </summary>
        /// <param name="itemID">ID of the item quantity to modify</param>
        /// <param name="quantity">number of items you are adding or subtracting from the current quantity</param>
        /// <param name="reasonID"></param>
        /// <param name="UserID"></param>
        /// <param name="storeID"></param>
        /// <param name="subUnitID"></param>
        /// <returns>table 1 - errors, table 2 - true/false</returns>
        public DataSet AdjustItemQuantity(int itemID, int quantity, int reasonID, int userID, int storeID, int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_adjustItemQuantity");
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@quantity", quantity);
            command.Parameters.AddWithValue("@reasonID", reasonID);
            command.Parameters.AddWithValue("@userID", userID);
            //command.Parameters.AddWithValue("@storeID", storeID); // No longer needed with new version of sp_adjustItemQuantity
            //command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Gets all adjustment reasons
        /// </summary>
        /// <returns>Table 1 - errors, table 2 - int adjReasonID, int adjNam</returns>
        public DataSet GetItemAdjustmentReasons()
        {
            SqlCommand command = new SqlCommand("sp_getItemAdjustmentReasons");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }


        #endregion

        #region reports

        //Michael Meyer
        /// <summary>
        /// Returns a dataset for all the items adjustments
        /// </summary>
        /// <param name="startDate">Beginning date for the report</param>
        /// <param name="endDate">End date for the report</param>
        /// <returns>dataset - string storeName, string firstName, string lastName, int itemID, string itemName, string reasonForChange, int quantity</returns>
        public DataSet ReportItemAdjustments(DateTime startDate, DateTime endDate)
        {
            SqlCommand command = new SqlCommand("sp_reports_itemAdjustmentsByDateRange");
            command.Parameters.AddWithValue("@startDate", startDate);
            command.Parameters.AddWithValue("@endDate", endDate);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Returns a dataset for all the items in the database for a report
        /// </summary>
        /// <param name="startDate">Beginning date for the report</param>
        /// <param name="endDate">End date for the report</param>
        /// <param name="preparedBy">Who prepared the report</param>
        /// <returns>dataset for a report</returns>
        public DataSet ReportItemsByDate(DateTime startDate, DateTime endDate, string preparedBy)
        {
            SqlCommand command = new SqlCommand("sp_report_itemReportForDateRange");
            command.Parameters.AddWithValue("@startDate", startDate);
            command.Parameters.AddWithValue("@endDate", endDate);
            command.Parameters.AddWithValue("@preparedBy", preparedBy);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// List of years for the reports
        /// </summary>
        /// <returns>Returns a table with a list of years.</returns>
        public DataSet GetYears()
        {
            SqlCommand command = new SqlCommand("sp_getYears");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Generates a report for a current item getting current quantity etc.
        /// </summary>
        /// <param name="itemID">Id of the item that you want to get the information about</param>
        /// <returns>int rowNumber, int itemName, int totalQuantity, float markupCost, string itemCategoryName, float vendorCost</returns>
        public DataSet ReportCurrentItemByItemID(int itemID)
        {
            SqlCommand command = new SqlCommand("sp_reports_currentItemByItemID");
            command.Parameters.AddWithValue("@itemID", itemID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Returns the current items and quantities for the report.
        /// </summary>
        /// <returns>table 1 - errors, table 2 - int itemName, int totalQuantity, float markupCost, string itemCategoryName, float vendorCost</returns>
        public DataSet ReportCurrentItems()
        {
            SqlCommand command = new SqlCommand("sp_reports_currentItems");
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Michael Meyer
        /// <summary>
        /// Returns the current items and quantities for the report by subunit
        /// </summary>
        /// <returns>table 1 - errors, table 2 - int itemName, int totalQuantity, float markupCost, string itemCategoryName, float vendorCost</returns>
        public DataSet ReportCurrentItemsBySubUnit(int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_reports_currentItemsBySubUnit");
            command.Parameters.AddWithValue("@subUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }
        //Michael Meyer
        /// <summary>
        /// Returns the current items and quantities for the report by category
        /// </summary>
        /// <returns>table 1 - errors, table 2 - int itemName, int totalQuantity, float markupCost, string itemCategoryName, float vendorCost</returns>
        public DataSet ReportCurrentItemsByCategory(int categoryID)
        {
            SqlCommand command = new SqlCommand("sp_reports_currentItemsByCategory");
            command.Parameters.AddWithValue("@categoryID", categoryID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion

        #region allocation
        //Michael Meyer
        /// <summary>
        /// Allocates an item for a storeUnit (mailbox)
        /// </summary>
        /// <param name="storeID">Store the item belongs to</param>
        /// <param name="subUnitID">Subunit that the store belongs to</param>
        /// <param name="itemID">Item in the store that you want to update</param>
        /// <param name="itemQuantity">The quantity that you want to add</param>
        /// <returns>table 1 - errors, table 2 - true/false</returns>
        public DataSet AllocateItem(int storeID, int emergencyQuantity, /*int itemUnit,*/ int itemID, int itemQuantity)
        {
            SqlCommand command = new SqlCommand("sp_allocateItem");
            command.Parameters.AddWithValue("@storeID", storeID);
            command.Parameters.AddWithValue("@EmergencyQuantity", emergencyQuantity);
            command.Parameters.AddWithValue("@itemID", itemID);
            command.Parameters.AddWithValue("@itemQuantity", itemQuantity);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Preston Berrett
        public DataSet GetAllItemsForStoreID(int storeID, int subUnitID)
        {
            SqlCommand command = new SqlCommand("sp_getAllItemsByStoreID");
            command.Parameters.AddWithValue("@StoreID", storeID);
            command.Parameters.AddWithValue("@SubUnitID", subUnitID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion


        public DataSet UpdateEmergencyQuantity(int itemID, int qty)
        {
            SqlCommand command = new SqlCommand("sp_updateEmergencyQuantity");
            command.Parameters.AddWithValue("@ItemID", itemID);
            command.Parameters.AddWithValue("@Qty", qty);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet GetCategoryByStore(int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_getCategoryByStore");
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        //Created by Adam Venezia 10-21-12, helps with CEO signoff print off.
        public DataSet GetRequisitionByRequisitionID(int rID)
        {
            SqlCommand command = new SqlCommand("sp_getRequisitionByRequisitionID");
            command.Parameters.AddWithValue("@RequisitionID", rID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet SubmitOutside(int ORequisitionID, int OVendorID, String OrderNo)
        {
            SqlCommand command = new SqlCommand("sp_submitOutsideProcurement");
            command.Parameters.AddWithValue("@OrderNo", OrderNo);
            command.Parameters.AddWithValue("@VendorID", OVendorID);
            command.Parameters.AddWithValue("@RequisitionID", ORequisitionID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet CheckOpenOutsideProcurement(int StoreID)
        {
            SqlCommand command = new SqlCommand("sp_checkOpenOutsideProcurement");
            command.Parameters.AddWithValue("@StoreID", StoreID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        public DataSet InsertOutsideProcureToInventory(int outsideID, String orderNo, int vendorID)
        {
            SqlCommand command = new SqlCommand("sp_insertIntoInventory");
            command.Parameters.AddWithValue("@RequisitionID", outsideID);
            command.Parameters.AddWithValue("@OrderNo", orderNo);
            command.Parameters.AddWithValue("@VendorID", vendorID);
            command.CommandType = CommandType.StoredProcedure;

            return GetDataSet(command);
        }

        #endregion
    }
}